Creating Tables with Visual FoxPro 

  • Article

 

Microsoft Corporation

March 2001

Applies To: Microsoft Visual FoxPro 7.0

Summary: With Visual FoxPro, you can create and manage tables of data individually and relationally from many sources. You can use either the Visual FoxPro language or the many tools to access or manipulate all kinds of data quickly, whether it is on a local disk, a network, or the Internet. (5 printed pages)

Contents

Introduction What Is Data? Importing Data into Tables Arranging Data in Tables Accessing Data in Tables

Introduction

Microsoft® Visual FoxPro® 7.0 is an application, a language, and a set of application development tools. Visual FoxPro tools are written in the Visual FoxPro language and are themselves based on tables.

In Visual FoxPro, you can use wizards, builders, or commands to create or manipulate tables of data quickly. You can use the Import and Table wizards to create tables, the Table designer to manage tables, and the Visual FoxPro Browse command to view tables.

For simple access to data, use Visual FoxPro directly from commands, menus, and the Data Session window. For more complex data access and manipulation, you can create forms, reports, or views to enter and display tables.

This paper describes how to access existing data with the Import wizard and how to create the structure for new data using the Table wizard and the Table designer from Visual FoxPro menus.

What Is Data?

Visual FoxPro works with and manipulates data in tables. The data can be comprised of any of the following types of information, either alone or in combinations: Character, Currency, Date, DateTime, Logical, Numeric, Float, and Double. Visual FoxPro also accesses objects (such as Microsoft Word and Microsoft Excel documents), graphics, and sound files, as data.

You can use data that resides in many different kinds of tables, including tab or comma-delimited text files or tables produced in other applications, such as Excel or Microsoft® SQL Server.

For more information on valid data types, see "Data and Field Types" in the Visual FoxPro Help.

Importing Data into Tables

To get data that is already in tabular format, open Visual FoxPro and import the file using the Import wizard. If you want to create the data in Visual FoxPro, use the Table wizard.

The Import Wizard

The Import wizard gives you the option of importing text files in which data is delimited by commas, spaces, tabs, or other separators.

When you use the Import wizard to copy the data into a new Visual FoxPro table, you must know how the text is delimited. In addition, you must make sure the same character delimits all the fields or your data will not import correctly.

To use the Import wizard:

  1. Select the Import option from the File menu.
  2. Click the Wizard button in the Import dialog box.

You can use this wizard for other data formats, but it is especially good for text files. For example, if your data is in a file created in Microsoft Excel, you can select Excel from the list provided in the Import wizard. If you are more familiar with another data-generating application, it might be easier to output a delimited text file from that application for importing into Visual FoxPro.

The Table Wizard

When you use the Table wizard to create a new Visual FoxPro table, you must know the type and maximum size of the data that you plan to place in each field. In addition, you should consider how the data will be entered and used so you can decide whether to allow null values.

The Table wizard provides many templates that you can use or customize:

  • Business templates: The wizard offers 26 templates for data you might use in commercial environments and transactions.
  • Personal templates: The wizard offers 20 templates for items or activities related to your hobbies, household, or personal possessions.

To use the Table wizard:

  1. Select New in the File menu.

  2. In the New dialog box, click Table, and then click the Wizard button.

    **Note   **The Newfile option also makes it possible for you to create a table, but you must do more of the work unprompted.

After you select a template, follow the remainder of the wizard steps to select fields, specify whether and how the table is indexed, and specify whether the table is a free table or contained within a database. When you complete the wizard, Visual FoxPro creates the empty table structure you have specified. You then can save the table structure and view the table, modify the new table in the Table designer, or move on to something else entirely.

The Table Designer

If you want to create a table without a wizard, use the Table designer. In the Table designer, you can enter the name, data type, and size of each field. In addition to these characteristics, you also have opportunities to specify the decimal value width of numeric fields, whether there is an index, and whether NULL values are allowed.

If your table is in a database, you can specify input and display formats, field validation rules, and value tip messages, as well as setup your table to behave in specific ways whenever you use fields from the table on a form.

To access the Table designer:

  1. Select New in the File menu.
  2. In the New dialog box, click the New File button.

Arranging Data in Tables

Visual FoxPro tables can be free or contained in a database. A database (.dbc) file can contain one or more tables, views, connections, and stored procedures.

You can access the data in a free table from anywhere, even from one or more databases. You can only access data in a database-contained table from within the database that contains it.

To open a free table and its database:

  1. From the File menu, select Open.

  2. In the New dialog box, select Table, and then click either the New File button or Wizard button.

    Note   If the table is contained in a database, the Open command opens the table and the containing database.

Although you have restricted access to databased tables, a database provides many properties that make this restriction worthwhile. One indication of this is when you use the Table designer to design a free table, the dialog box provides three simple tabs, but when you design a databased table, each tab provides significantly more options. For simple access and viewing, free tables are quite adequate.

Tables can be simple or complex. You can often make using data in tables more efficient through good planning—carefully designing not only the table structure, but also the table relationships.

For example, if you are designing an invoice tracking system, you could create one table to hold all of the information on each invoice. It would be more efficient, however, to create tables that contained information on customers (name, address, phone numbers, customer ID, and zip code), inventory (item, cost, price, quantity on hand, and supplier), suppliers (name, address, phone numbers, ID, discount info, and contact), and other categories (zip code, city, state, and so on). Then, these tables would combine to produce invoices at one time, vendor orders another time, and reports of various kinds, including billing statements, at other times. Each individual file would be more manageable. Duplications could be eliminated and daily or repetitive operations would take less computer and personnel time. Some data entry would be simplified, enhanced, or eliminated because the related tables would get the information automatically.

For more information on designing tables, see "Choosing Data Types" in the Visual FoxPro Help.

Accessing Data in Tables

There are countless ways to use or manipulate data in tables, but first you must access the tables.

The Data Session window provides the most effective way of accessing tables. Each table opens its own data session. You can use the menus or code to move from session to session, opening and viewing tables as you want, with two menu selections or commands for each table. However, if you use the Data Session window, you can open all the tables you want (to the limit of installed memory) without closing the window. The Data Session window also provides information on table properties and any relationships you might set up between tables

To clear the data environment:

  1. In the Command window, type the following: 

     CLOSE ALL TABLES

  2. Press ENTER.

To view table properties:

  1. From the Window menu, select Data Session.

  2. In the Data Session window, select a table.

    • If a table is already open, it is listed in the Aliases drop-down box so you can select it.
    • If no table is open, click Open to access the Open dialog box, which makes it possible for you to specify which table to open.
    • If the listed table is in a database and you click Open, the Open dialog box displays a list of the other tables in that database. You can select one from the list or click the Other button to specify a free table.

    **Note   **The Open dialog box shows available table (.dbf) files. You can open as many of these as you like. If you select a table that is part of a database, a second dialog box opens for the next table Open selection. This dialog box displays the tables available in the database you opened by selecting the table within it. If you want to select more free tables, click Other and then choose a table.

To access a table for viewing or editing:

  • In the Data Session window, select an open table, and then click the Browse button. You can continue until all the listed (open) tables are displayed.

    **Note   **Each time you browse a table in the Data Session window, the Command window displays a new SELECT statement followed by a BROWSE statement.

To view and modify Table characteristics:

  • In the Data Session window, select an open table, and then click the Properties button. This opens the Work Area dialog box, which provides information and access to the table structure and indexes.

In this environment, you can select any table and edit it, append and delete records, or modify the table structure.

When it opens, you can navigate the table, change or delete record contents, or add new data. Use View menu options to select the type of access you want. Use the Table menu options to navigate or modify the table.

**Note   **If you want more access control or complex manipulation of data, or if you want to use remote views, and related tables, create a database for your table. If you want to create an application, COM Server, or Web Service, using more features, such as labels, forms, reports, objects and class libraries, create a Visual FoxPro Project (starting in a Project is the best way any time).

For more information on using tables, see "Working with Tables" in the Visual FoxPro Help. For more information on the Data Session window, see "Using Data Sessions" in the Visual FoxPro Help.

© Microsoft Corporation. All rights reserved.